package com.java2007.zhangyu.restaurant.dao.impl;

import com.java2007.zhangyu.restaurant.dao.FoodDao;
import com.java2007.zhangyu.restaurant.pojo.Food;
import com.java2007.zhangyu.restaurant.pojo.FoodType;
import com.java2007.zhangyu.restaurant.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.xml.ws.handler.Handler;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zy
 * @Version
 * @date 2021/01/27
 */
public class FoodDaoImpl implements FoodDao {
    @Override
    public int save(Food food) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "INSERT INTO t_food(f_type_id,f_name,f_price,f_vip_price,f_image,f_info) VALUES(?,?,?,?,?,?)";

        return runner.update(JdbcUtils.getConnection(),sql,food.getFtId(),food.getfName(),food.getfPrice(),food.getfVipPrice(),food.getfImage(),food.getfInfo());
    }

    @Override
    public int deleteByFoodId(Integer fID) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "DELETE FROM t_food WHERE f_id = ?;";

        return runner.update(JdbcUtils.getConnection(),sql,fID);
    }

    @Override
    public int update(Food food) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "UPDATE t_food SET f_name = ?,f_type_id = ?,f_price = ?,f_vip_price = ?,f_image = ?,f_info = ? WHERE f_id = ?";

        return runner.update(JdbcUtils.getConnection(),sql,food.getfName(),food.getFtId(),food.getfPrice(),food.getfVipPrice(),food.getfImage(),food.getfInfo(),food.getfId());
    }

    @Override
    public Food queryByFoodId(Integer fId) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "SELECT f_id fId,f_type_id ftId,f_name fName,f_price fPrice,f_vip_price fVipPrice,f_image fImage,f_info fInfo FROM t_food WHERE f_id = ?";

        return runner.query(JdbcUtils.getConnection(),sql,new BeanHandler<>(Food.class),fId);
    }

    @Override
    public List<Food> queryByFoodName(String fName) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "SELECT f_id fId,ft_name ftName,f_type_id ftId,f_name fName,f_price fPrice,f_vip_price fVipPrice,f_info fInfo\n" +
                "FROM t_food f\n" +
                "LEFT JOIN t_food_type ft \n" +
                "ON f.f_type_id = ft.ft_id\n" +
                "WHERE f.f_name LIKE ?";

        return runner.query(JdbcUtils.getConnection(),sql,new BeanListHandler<>(Food.class),fName);
    }

    @Override
    public List<Food> queryAll() throws Exception {
        QueryRunner runner = new QueryRunner();

        String sql = "SELECT f_id fId,f_name fName,f_type_id ftId,f_price fPrice,f_vip_price fVipPrice FROM t_food";

        return runner.query(JdbcUtils.getConnection(),sql,new BeanListHandler<>(Food.class));
    }

    @Override
    public List<Food> queryPage(Integer startPage, Integer pageSize,Food food) throws Exception {

        List<Object> params = new ArrayList<>();

        QueryRunner runner = new QueryRunner();

        StringBuffer sql = new StringBuffer("SELECT f_id fId,f_type_id ftId,f_name fName," +
                "f_price fPrice,f_vip_price fVipPrice,f_image fImage,f_info fInfo FROM t_food where 1=1");

        if(food.getFtId() != null) {
            sql.append(" AND f_type_id = ? ");
            params.add(food.getFtId());
        }
        if(null != food.getfName()) {
            sql.append(" AND f_name LIKE ? ");
            params.add("%" + food.getfName() + "%");
        }
        sql.append(" limit ?,?");
        params.add(startPage);
        params.add(pageSize);

        System.out.println("sql: " + sql.toString());
        System.out.println("参数: " + params);

        return runner.query(JdbcUtils.getConnection(),sql.toString(), new BeanListHandler<>(Food.class), params.toArray());
    }

    @Override
    public Long getCount(Food food) throws SQLException {
        List<Object> params = new ArrayList<>();
        QueryRunner runner = new QueryRunner();
        StringBuffer sql = new StringBuffer("SELECT count(1) FROM t_food where 1=1");
        if(food.getFtId() != null) {
            sql.append(" AND f_id = ? ");
            params.add(food.getFtId());
        }
        if(null != food.getfName()) {
            sql.append(" AND f_name LIKE ? ");
            params.add("%" + food.getfName() + "%");
        }
        return runner.query(JdbcUtils.getConnection(),sql.toString(), new ScalarHandler<Long>(), params.toArray());
    }

    @Override
    public Food selectFoodByFName(String fName) throws SQLException {
        QueryRunner runner = new QueryRunner();

        String sql = "SELECT f_id fId, f_name fName,f_type_id ftId,f_price fPrice, f_vip_price fVipPrice \n" +
                "FROM t_food \n" +
                "WHERE f_name = ?";

        return runner.query(JdbcUtils.getConnection(),sql,new BeanHandler<>(Food.class),fName);
    }
}
